#nullable enable

using System.Data.Common;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using PHMEE.CommonUtils;
using PHMEE.TDengineInfrastructure;
using TDengine.Data.Client;

namespace PHMEE.EnergyEfficiency;

public class BunkerReportRepository : BaseTdRepository, IBunkerReportRepository
{
    /// <summary>
    /// LogAbstract 位于能效库
    /// </summary>
    private const DbPrefixEnum DbPrefix = DbPrefixEnum.Ee;

    /// <summary>
    /// LogAbstract 约定好的子表名前缀
    /// </summary>
    private const string SubTablePrefix = "br";

    /// <summary>
    /// 各 sql 语句的 VALUES 前的部分，含 id
    /// </summary>
    private const string SubTableValues =
        "ts,id,bunker_delivery_note_number,bunker_delivery_date_time,fuel_type,mass,creation_time,last_modified_time";
    private readonly ILogger<BunkerReportRepository> _logger;

    public BunkerReportRepository(IOptionsSnapshot<TDengineOptions> tdengineOptions,
        ILogger<BunkerReportRepository> logger) : base(tdengineOptions)
    {
        _logger = logger;
    }

    /// <summary>
    /// 获取子表名
    /// </summary>
    /// <param name="vesselId"></param>
    /// <returns></returns>
    private static string GetSubTableName(Guid vesselId)
    {
        return $"{SubTablePrefix}_{vesselId:N}";
    }

    public async Task<BunkerReport?> GetAsync(Guid vesselId, DateTime dateTimeUtc)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);
            var subTableName = GetSubTableName(vesselId);

            await using var command = TDConnection.CreateCommand();

            command.CommandText = $"SELECT {SubTableValues} from {dbName}.{subTableName} WHERE ts = '{dateTimeUtc.ToFormattedUtcString()}' ORDER BY ts DESC LIMIT 1;";

            await using var reader = await command.ExecuteReaderAsync();

            if (await reader.ReadAsync())
            {
                return MapFromDbReader(reader);
            }

            return null;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(BunkerReportRepository)}.{nameof(GetAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, dateTimeUtc, null, null, "根据时间查询 BunkerReport 数据失败", ex);
        }
    }

    /// <summary>
    /// 计数，时间前闭后开区间
    /// </summary>
    /// <param name="vesselId"></param>
    /// <param name="startDateTimeUtc"></param>
    /// <param name="endDateTimeUtc"></param>
    /// <returns></returns>
    public async Task<int> CountAsync(Guid vesselId, DateTime startDateTimeUtc, DateTime endDateTimeUtc)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            var subTableName = GetSubTableName(vesselId);

            await using var command = TDConnection.CreateCommand();

            command.CommandText =
                $"SELECT COUNT(*) FROM {dbName}.{subTableName} WHERE ts >= '{startDateTimeUtc.ToFormattedUtcString()}' AND ts < '{endDateTimeUtc.ToFormattedUtcString()}';";

            var result = await command.ExecuteScalarAsync();

            if (result is null)
            {
                throw new TDengineRepositoryException(vesselId, null, startDateTimeUtc, endDateTimeUtc,
                    "查询 BunkerReport Count 失败，result 为 null");
            }

            var count = int.Parse(result.ToString()!);
            return count;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(BunkerReportRepository)}.{nameof(CountAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, startDateTimeUtc, endDateTimeUtc,
                "查询 BunkerReport Count 失败", ex);
        }
    }

    /// <summary>
    /// 时间前闭后开区间查询，按 Ts DESC 排序
    /// </summary>
    /// <param name="vesselId"></param>
    /// <param name="endDateTimeUtc"></param>
    /// <param name="skipCount"></param>
    /// <param name="maxResultCount"></param>
    /// <param name="startDateTimeUtc"></param>
    /// <returns></returns>
    public async Task<List<BunkerReport>> GetListDescAsync(Guid vesselId, DateTime startDateTimeUtc,
        DateTime endDateTimeUtc, int skipCount, int maxResultCount)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            var subTableName = GetSubTableName(vesselId);

            await using var command = TDConnection.CreateCommand();

            command.CommandText =
                $"SELECT {SubTableValues} FROM {dbName}.{subTableName} WHERE ts >= '{startDateTimeUtc.ToFormattedUtcString()}' AND ts < '{endDateTimeUtc.ToFormattedUtcString()}' ORDER BY ts DESC LIMIT {maxResultCount} OFFSET {skipCount};";

            await using var reader = await command.ExecuteReaderAsync();

            List<BunkerReport> list = [];

            while (await reader.ReadAsync())
            {
                BunkerReport newInstance = MapFromDbReader(reader);
                list.Add(newInstance);
            }

            return list;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(BunkerReportRepository)}.{nameof(GetListDescAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, startDateTimeUtc, endDateTimeUtc,
                "查询 BunkerReport List DESC 失败", ex);
        }
    }

    /// <summary>
    /// 获取（不根据设备 id 筛选） [startTime, endTime) 之间的记录（不包括 Tags 的值，只包含表的列），按时间从早到晚顺序
    /// </summary>
    /// <param name="vesselId">船舶 Id，用于确定数据库名</param>
    /// <param name="startDateTimeUtc"></param>
    /// <param name="endDateTimeUtc"></param>
    /// <returns></returns>
    public async Task<List<BunkerReport>> GetListASCAsync(Guid vesselId, DateTime startDateTimeUtc, DateTime endDateTimeUtc)
    {
        List<BunkerReport> list = [];

        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);

            var subTableName = GetSubTableName(vesselId);

            await using var command = TDConnection.CreateCommand();

            command.CommandText = $"SELECT {SubTableValues} FROM {dbName}.{subTableName} WHERE ts >= '{startDateTimeUtc.ToFormattedUtcString()}' AND ts < '{endDateTimeUtc.ToFormattedUtcString()}' ORDER BY ts ASC;";

            await using var reader = await command.ExecuteReaderAsync();

            while (await reader.ReadAsync())
            {
                BunkerReport newInstance = MapFromDbReader(reader);
                list.Add(newInstance);
            }

            return list;
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(BunkerReportRepository)}.{nameof(GetListASCAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, null, startDateTimeUtc, endDateTimeUtc, "查询 BunkerReport List ASC 失败", ex);
        }
    }

    #region 增删相关函数

    /// <summary>
    /// 插入数据
    /// </summary>
    /// <param name="bunkerReport"></param>
    /// <param name="vesselId"></param>
    public async Task InsertAsync(BunkerReport bunkerReport, Guid vesselId)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);
            var subTableName = GetSubTableName(vesselId);

            await using var command = TDConnection.CreateCommand();

            var (insertCommandText, insertCommandParameters) = GetInsertCommandTextAndParameters(bunkerReport, dbName, subTableName);

            command.CommandText = insertCommandText;

            foreach (var (parameterName, parameterValue) in insertCommandParameters)
            {
                command.Parameters.Add(new TDengineParameter(parameterName, parameterValue));
            }

            await command.ExecuteNonQueryAsync();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(BunkerReportRepository)}.{nameof(InsertAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, bunkerReport.Ts, null, null, "插入 BunkerReport 数据失败", ex);
        }
    }

    /// <summary>
    /// 删除
    /// </summary>
    /// <param name="vesselId"></param>
    /// <param name="ts"></param>
    public async Task DeleteAsync(Guid vesselId, DateTime ts)
    {
        try
        {
            var dbName = PhmeeConst.GetDbName(DbPrefix, vesselId);
            var subTableName = GetSubTableName(vesselId);

            await using var command = TDConnection.CreateCommand();

            var sql = $"DELETE FROM {dbName}.{subTableName} WHERE ts = '{ts.ToFormattedUtcString()}';";

            command.CommandText = sql;

            await command.ExecuteNonQueryAsync();
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, $"TDengine 仓储: {nameof(BunkerReportRepository)}.{nameof(DeleteAsync)} 失败");
            throw new TDengineRepositoryException(vesselId, ts, null, null, "删除 BunkerReport 数据失败", ex);
        }
    }

    #endregion 增删相关函数

    private static BunkerReport MapFromDbReader(DbDataReader reader)
    {
        var result = new BunkerReport();

        result.Ts = reader.GetDateTime(reader.GetOrdinal("ts")).ToUniversalTime();
        result.Id = Guid.Parse(System.Text.Encoding.UTF8.GetString((byte[])reader.GetValue(reader.GetOrdinal("id"))));
        result.BunkerDeliveryNoteNumber = (string)reader.GetValue(reader.GetOrdinal("bunker_delivery_note_number"));
        result.BunkerDeliveryDateTime = reader.GetDateTime(reader.GetOrdinal("bunker_delivery_date_time")).ToUniversalTime();
        result.FuelType = (FuelType)reader.GetInt32(reader.GetOrdinal("fuel_type"));
        result.Mass = reader.GetValue(reader.GetOrdinal("mass")) as float? ?? default;
        result.CreationTime = reader.GetDateTime(reader.GetOrdinal("creation_time")).ToUniversalTime();
        result.LastModifiedTime = reader.GetDateTime(reader.GetOrdinal("last_modified_time")).ToUniversalTime();

        return result;
    }

    private static (string insertCommandText, List<KeyValuePair<string, object?>> insertCommandParameters) GetInsertCommandTextAndParameters(BunkerReport bunkerReport, string dbName, string subTableName)
    {
        string commandText = $"INSERT INTO {dbName}.{subTableName} USING {dbName}.bunker_report (id) TAGS ('{bunkerReport.Id}') (ts, bunker_delivery_note_number, bunker_delivery_date_time, fuel_type, mass, creation_time, last_modified_time) VALUES (?, ?, ?, ?, ?, ?, ?);";

        List<KeyValuePair<string, object?>> insertCommandParameters = [
            new ("@0", DateTime.SpecifyKind(bunkerReport.Ts, DateTimeKind.Utc)),
            new ("@1", bunkerReport.BunkerDeliveryNoteNumber),
            new ("@2", DateTime.SpecifyKind(bunkerReport.BunkerDeliveryDateTime, DateTimeKind.Utc)),
            new ("@3", (int)bunkerReport.FuelType),
            new ("@4", bunkerReport.Mass),
            new ("@5", DateTime.SpecifyKind(bunkerReport.CreationTime, DateTimeKind.Utc)),
            new ("@6", DateTime.SpecifyKind(bunkerReport.LastModifiedTime, DateTimeKind.Utc)),
        ];

        return (commandText, insertCommandParameters);
    }
}